
/* Result Sets Interface */
#ifndef SQL_CRSR
#  define SQL_CRSR
  struct sql_cursor
  {
    unsigned int curocn;
    void *ptr1;
    void *ptr2;
    unsigned int magic;
  };
  typedef struct sql_cursor sql_cursor;
  typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */

/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;

/* Object support */
struct sqltvn
{
  unsigned char *tvnvsn; 
  unsigned short tvnvsnl; 
  unsigned char *tvnnm;
  unsigned short tvnnml; 
  unsigned char *tvnsnm;
  unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;

struct sqladts
{
  unsigned int adtvsn; 
  unsigned short adtmode; 
  unsigned short adtnum;  
  sqltvn adttvn[1];       
};
typedef struct sqladts sqladts;

static struct sqladts sqladt = {
  1,1,0,
};

/* Binding to PL/SQL Records */
struct sqltdss
{
  unsigned int tdsvsn; 
  unsigned short tdsnum; 
  unsigned char *tdsval[1]; 
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
  1,
  0,
};

/* File name & Package Name */
struct sqlcxp
{
  unsigned short fillen;
           char  filnam[12];
};
static const struct sqlcxp sqlfpn =
{
    11,
    "assign72.pc"
};


static unsigned int sqlctx = 148379;


static struct sqlexd {
   unsigned long  sqlvsn;
   unsigned int   arrsiz;
   unsigned int   iters;
   unsigned int   offset;
   unsigned short selerr;
   unsigned short sqlety;
   unsigned int   occurs;
      const short *cud;
   unsigned char  *sqlest;
      const char  *stmt;
   sqladts *sqladtp;
   sqltdss *sqltdsp;
   unsigned char  **sqphsv;
   unsigned long  *sqphsl;
            int   *sqphss;
            short **sqpind;
            int   *sqpins;
   unsigned long  *sqparm;
   unsigned long  **sqparc;
   unsigned short  *sqpadto;
   unsigned short  *sqptdso;
   unsigned int   sqlcmax;
   unsigned int   sqlcmin;
   unsigned int   sqlcincr;
   unsigned int   sqlctimeout;
   unsigned int   sqlcnowait;
            int   sqfoff;
   unsigned int   sqcmod;
   unsigned int   sqfmod;
   unsigned char  *sqhstv[9];
   unsigned long  sqhstl[9];
            int   sqhsts[9];
            short *sqindv[9];
            int   sqinds[9];
   unsigned long  sqharm[9];
   unsigned long  *sqharc[9];
   unsigned short  sqadto[9];
   unsigned short  sqtdso[9];
} sqlstm = {12,9};

// Prototypes
extern "C" {
  void sqlcxt (void **, unsigned int *,
               struct sqlexd *, const struct sqlcxp *);
  void sqlcx2t(void **, unsigned int *,
               struct sqlexd *, const struct sqlcxp *);
  void sqlbuft(void **, char *);
  void sqlgs2t(void **, char *);
  void sqlorat(void **, unsigned int *, void *);
}

// Forms Interface
static const int IAPSUCC = 0;
static const int IAPFAIL = 1403;
static const int IAPFTL  = 535;
extern "C" { void sqliem(char *, int *); }

typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;

/* cud (compilation unit data) array */
static const short sqlcud0[] =
{12,4130,1,0,0,
5,0,0,1,0,0,27,152,0,0,4,4,0,1,0,1,9,0,0,1,9,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,0,0,17,206,0,0,1,1,0,1,0,1,9,0,0,
55,0,0,2,0,0,45,213,0,0,0,0,0,1,0,
70,0,0,2,0,0,13,226,0,0,9,0,0,1,0,2,3,0,0,2,9,0,0,2,9,0,0,2,3,0,0,2,9,0,0,2,9,
0,0,2,9,0,0,2,9,0,0,2,3,0,0,
121,0,0,3,0,0,17,279,0,0,1,1,0,1,0,1,9,0,0,
140,0,0,3,0,0,21,282,0,0,0,0,0,1,0,
155,0,0,3,0,0,17,301,0,0,1,1,0,1,0,1,9,0,0,
174,0,0,2,0,0,13,306,0,0,4,0,0,1,0,2,9,0,0,2,3,0,0,2,9,0,0,2,9,0,0,
205,0,0,4,0,0,30,402,0,0,0,0,0,1,0,
220,0,0,5,0,0,32,414,0,0,0,0,0,1,0,
};


/**
	Name                :  Stephen Durfey
	Course Number: CS462
	Semester         :  Spring 2009
	Assignment      : 7
	Due Date          : Friday, April 3rd, 12:00pm
	
	Description: This program is a simple database application that will perform specified select statements on a set of three relations
			DEPARTMENT, INTERSECTION, and FACULTY. The user will be able to select what type of query
			they would like to perform, they have two options: a query on the department, which will return a list of all faculty members in
			that department (if any), and a query on a particular faculty member, which will return all departments associated with that 
            faculty member. If no results are found, the user is
            notified. modified*****
*/

#include <iostream>
#include <string>
#include <iomanip>
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <unistd.h>

using namespace std;

//Declare Section

/* EXEC SQL BEGIN DECLARE SECTION; */ 


	/* VARCHAR   username[20]; */ 
struct { unsigned short len; unsigned char arr[20]; } username;

	/* VARCHAR   password[20]; */ 
struct { unsigned short len; unsigned char arr[20]; } password;

	/* VARCHAR   sqlStatement[800]; */ 
struct { unsigned short len; unsigned char arr[800]; } sqlStatement;


	int       DS_DeptNum;
	/* VARCHAR   DS_DeptName[30]; */ 
struct { unsigned short len; unsigned char arr[30]; } DS_DeptName;

	/* VARCHAR   DS_DeptLocation[30]; */ 
struct { unsigned short len; unsigned char arr[30]; } DS_DeptLocation;

	/* VARCHAR   DS_DeptPhone[12]; */ 
struct { unsigned short len; unsigned char arr[12]; } DS_DeptPhone;


	int       DS_FacNum;
	int       DS_FacYearStart;
	/* VARCHAR   DS_FacName[20]; */ 
struct { unsigned short len; unsigned char arr[20]; } DS_FacName;

	/* VARCHAR   DS_FacRank[4]; */ 
struct { unsigned short len; unsigned char arr[4]; } DS_FacRank;


	int       DS_IDeptNum;
	int       DS_IFacNum;
	/* VARCHAR   DS_IOffice[30]; */ 
struct { unsigned short len; unsigned char arr[30]; } DS_IOffice;

	/* VARCHAR   DS_IPhone[12]; */ 
struct { unsigned short len; unsigned char arr[12]; } DS_IPhone;


/* EXEC SQL END DECLARE SECTION; */ 


//include SQL communications area

/* EXEC SQL INCLUDE SQLCA;
 */ 
/*
 * $Header: sqlca.h 24-apr-2003.12:50:58 mkandarp Exp $ sqlca.h 
 */

/* Copyright (c) 1985, 2003, Oracle Corporation.  All rights reserved.  */
 
/*
NAME
  SQLCA : SQL Communications Area.
FUNCTION
  Contains no code. Oracle fills in the SQLCA with status info
  during the execution of a SQL stmt.
NOTES
  **************************************************************
  ***                                                        ***
  *** This file is SOSD.  Porters must change the data types ***
  *** appropriately on their platform.  See notes/pcport.doc ***
  *** for more information.                                  ***
  ***                                                        ***
  **************************************************************

  If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
  will be defined to have this storage class. For example:
 
    #define SQLCA_STORAGE_CLASS extern
 
  will define the SQLCA as an extern.
 
  If the symbol SQLCA_INIT is defined, then the SQLCA will be
  statically initialized. Although this is not necessary in order
  to use the SQLCA, it is a good pgming practice not to have
  unitialized variables. However, some C compilers/OS's don't
  allow automatic variables to be init'd in this manner. Therefore,
  if you are INCLUDE'ing the SQLCA in a place where it would be
  an automatic AND your C compiler/OS doesn't allow this style
  of initialization, then SQLCA_INIT should be left undefined --
  all others can define SQLCA_INIT if they wish.

  If the symbol SQLCA_NONE is defined, then the SQLCA variable will
  not be defined at all.  The symbol SQLCA_NONE should not be defined
  in source modules that have embedded SQL.  However, source modules
  that have no embedded SQL, but need to manipulate a sqlca struct
  passed in as a parameter, can set the SQLCA_NONE symbol to avoid
  creation of an extraneous sqlca variable.
 
MODIFIED
    lvbcheng   07/31/98 -  long to int
    jbasu      12/12/94 -  Bug 217878: note this is an SOSD file
    losborne   08/11/92 -  No sqlca var if SQLCA_NONE macro set 
  Clare      12/06/84 - Ch SQLCA to not be an extern.
  Clare      10/21/85 - Add initialization.
  Bradbury   01/05/86 - Only initialize when SQLCA_INIT set
  Clare      06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/
 
#ifndef SQLCA
#define SQLCA 1
 
struct   sqlca
         {
         /* ub1 */ char    sqlcaid[8];
         /* b4  */ int     sqlabc;
         /* b4  */ int     sqlcode;
         struct
           {
           /* ub2 */ unsigned short sqlerrml;
           /* ub1 */ char           sqlerrmc[70];
           } sqlerrm;
         /* ub1 */ char    sqlerrp[8];
         /* b4  */ int     sqlerrd[6];
         /* ub1 */ char    sqlwarn[8];
         /* ub1 */ char    sqlext[8];
         };

#ifndef SQLCA_NONE 
#ifdef   SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
         struct sqlca sqlca
#endif
 
#ifdef  SQLCA_INIT
         = {
         {'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
         sizeof(struct sqlca),
         0,
         { 0, {0}},
         {'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
         {0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0}
         }
#endif
         ;
#endif
 
#endif
 
/* end SQLCA */


//function declarations

void connect();  //connects to Oracle
void buildSqlSelectDepartment( string department ); //builds specified sql statement for a department query
void buildSqlSelectFaculty( string facultyNum );    //builds specified sql statement for a faculty member query
void prepareCursor(); //prepares cursor
void executeSQL( string queryType ); //executes sql statement
void printResultsDepartment( int counter ); //prints out results from select statement on a department
void printResultsFaculty( int counter ); //prints out results from select statement on a faculty member
void commit(); //severs Oracle connection
void sqlErrorHandle(char *); //handles errors in SQL code
void notFound( ); //called whenver the select statement doesnt return any tuples
int executeDeleteSQL();
void deleteEmptyDepartment( string department );

int counter = 0;

int main()
{
	int decision = 0;
	string department,
		   facultyNum;
	
	//defines which function to call in case of an SQL error
	/* EXEC SQL WHENEVER SQLERROR do sqlErrorHandle("***Oracle Error:  "); */ 

	
	//connects to DB
	connect();
	
	cout << "Welcome to the interoffice database search application!\n";
	cout << "Specify a search type below, please\n\n";
	
	//displays menu for user to select options from, continues to loop until the user presses '3'
	//to exit the proram, which will call the commit function
	do
	{
		cout  << "(1) Department Query     (lists all faculty members within specified department number)\n";
		cout  << "(2) Faculty Member Query (lists all departments the specified employee number is associated with\n";
		cout  << "(3) Delete Empty Department\n";
		cout  << "(4) Exit\n";
		cout  << "Enter choice: ";
		cin   >> decision;

		if( decision == 1 )
		{
			cout << "\n\nEnter Department Number: ";
			cin  >> department;
			buildSqlSelectDepartment( department );
			prepareCursor();
			executeSQL( "department" );
		}
		else if( decision == 2 )
		{
			cout << "\n\nEnter Faculty Number: ";
			cin  >> facultyNum;
			buildSqlSelectFaculty( facultyNum );
			prepareCursor();
			executeSQL( "faculty" );
		}
		else if( decision == 3 )
		{
			cout << "\n\nEnter Department Number: ";
			cin  >> department;
			deleteEmptyDepartment(department);

		}
		
		if( decision != 1 && decision != 2 && decision != 3 )
		{
			cout << "I am sorry, I cannot process your request, please select the correct option from the menu, thank you\n\n\n";
		}
		
	}
	while( decision != 3 );
	
	commit();

	return 0;
}//end of main function

//asks for username and password, and attempts to connect to Oracle
void connect()
{
	//gathers the Oracle username and password
	char* temp_password;

	cout << "Enter Oracle Username: ";
	cin >> username.arr;

	temp_password = getpass("Enter Oracle Password: ");
	strcpy((char*)password.arr, temp_password);

	//gets length of username and password
	username.len = strlen( (char*) username.arr );
	password.len = strlen( (char*) password.arr );

	//connects to Oracle, with the specified username and password
	/* EXEC SQL CONNECT :username IDENTIFIED BY :password; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )10;
 sqlstm.offset = (unsigned int  )5;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (unsigned char  *)&username;
 sqlstm.sqhstl[0] = (unsigned long )22;
 sqlstm.sqhsts[0] = (         int  )22;
 sqlstm.sqindv[0] = (         short *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned long )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqhstv[1] = (unsigned char  *)&password;
 sqlstm.sqhstl[1] = (unsigned long )22;
 sqlstm.sqhsts[1] = (         int  )22;
 sqlstm.sqindv[1] = (         short *)0;
 sqlstm.sqinds[1] = (         int  )0;
 sqlstm.sqharm[1] = (unsigned long )0;
 sqlstm.sqadto[1] = (unsigned short )0;
 sqlstm.sqtdso[1] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlstm.sqlcmax = (unsigned int )100;
 sqlstm.sqlcmin = (unsigned int )2;
 sqlstm.sqlcincr = (unsigned int )1;
 sqlstm.sqlctimeout = (unsigned int )0;
 sqlstm.sqlcnowait = (unsigned int )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}



	cout << "Connected to Oracle as user: " << (char*) username.arr << endl << endl;

}//end of connect function


//builds the SQL select query, each time the function is called, the user is prompted to enter a  department number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectDepartment( string department )
{

	strcpy( (char*) sqlStatement.arr,  "SELECT   INTERSECTION.IDeptNum, DEPARTMENT.DeptName,\n"
									   "         DEPARTMENT.DeptLocation, INTERSECTION.IFacNum,\n"
									   "         FACULTY.FacName, INTERSECTION.IOffice,\n"
									   "         INTERSECTION.IPhone, FACULTY.FacRank, FACULTY.FacYearStart\n"
									   "FROM     DEPARTMENT\n"
									   "JOIN     INTERSECTION\n"
									   "ON       DEPARTMENT.DeptNum = INTERSECTION.IDeptNum\n"
									   "JOIN     FACULTY\n"
									   "ON       INTERSECTION.IFacNum = FACULTY.FacNum\n"
									   "WHERE    DEPARTMENT.DeptNum = ");

	strcat( (char*) sqlStatement.arr, department.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl; 
}//end of buildSqlSelectDepartment function

//builds the SQL select query, each time the function is called, the user is prompted to enter a faculty member number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectFaculty( string facultyNum )
{
	strcpy( (char*) sqlStatement.arr,  "SELECT   INTERSECTION.IDeptNum, DEPARTMENT.DeptName,\n"
									   "         DEPARTMENT.DeptLocation, INTERSECTION.IFacNum,\n"
									   "         FACULTY.FacName, INTERSECTION.IOffice,\n"
									   "         INTERSECTION.IPhone, FACULTY.FacRank, FACULTY.FacYearStart\n"
									   "FROM     FACULTY\n"
									   "JOIN     INTERSECTION\n"
									   "ON       FACULTY.FacNum = INTERSECTION.IFacNum\n"
									   "JOIN     DEPARTMENT\n"
									   "ON       INTERSECTION.IDeptNum = DEPARTMENT.DeptNum\n"
									   "WHERE    FACULTY.FacNum = ");

	strcat( (char*) sqlStatement.arr, facultyNum.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl;
} //end of buildSqlSelectFaculty function

void prepareCursor()
{
	sqlStatement.len = strlen( (char*) sqlStatement.arr );

	//prepares SQL select statement
	
	/* EXEC SQL PREPARE S1 FROM :sqlStatement; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.stmt = "";
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )36;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (unsigned char  *)&sqlStatement;
 sqlstm.sqhstl[0] = (unsigned long )802;
 sqlstm.sqhsts[0] = (         int  )0;
 sqlstm.sqindv[0] = (         short *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned long )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}



	//associate cursor with a prepared sql statement
	
	/* EXEC SQL DECLARE C1 CURSOR FOR S1; */ 


	//open the cursor, execute SQL statement, place tuples in the cursor
	/* EXEC SQL OPEN C1; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.stmt = "";
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )55;
 sqlstm.selerr = (unsigned short)1;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqcmod = (unsigned int )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


}//end of prepareCursor function

void executeSQL( string queryType )
{
	counter = 0; //keeps track on number of iterations, used in three functions, notFound, printResultsDepartment, and printResultsFaculty

	//loops through the cursor, and extracts the data, and stores that data into each variable in the
	//declare section, as specified 
	do
	{
		//if zero tuples were selected from DB, call the function notFound
		/* EXEC SQL WHENEVER NOT FOUND do notFound( ); */ 

		/* EXEC SQL FETCH C1 INTO  DS_IDeptNum,
								DS_DeptName,
								DS_DeptLocation,
								DS_IFacNum,
								DS_FacName,
								DS_IOffice,
								DS_IPhone,
								DS_FacRank,
								DS_FacYearStart; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 9;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )70;
  sqlstm.selerr = (unsigned short)1;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqfoff = (         int )0;
  sqlstm.sqfmod = (unsigned int )2;
  sqlstm.sqhstv[0] = (unsigned char  *)&DS_IDeptNum;
  sqlstm.sqhstl[0] = (unsigned long )sizeof(int);
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         short *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned long )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqhstv[1] = (unsigned char  *)&DS_DeptName;
  sqlstm.sqhstl[1] = (unsigned long )32;
  sqlstm.sqhsts[1] = (         int  )0;
  sqlstm.sqindv[1] = (         short *)0;
  sqlstm.sqinds[1] = (         int  )0;
  sqlstm.sqharm[1] = (unsigned long )0;
  sqlstm.sqadto[1] = (unsigned short )0;
  sqlstm.sqtdso[1] = (unsigned short )0;
  sqlstm.sqhstv[2] = (unsigned char  *)&DS_DeptLocation;
  sqlstm.sqhstl[2] = (unsigned long )32;
  sqlstm.sqhsts[2] = (         int  )0;
  sqlstm.sqindv[2] = (         short *)0;
  sqlstm.sqinds[2] = (         int  )0;
  sqlstm.sqharm[2] = (unsigned long )0;
  sqlstm.sqadto[2] = (unsigned short )0;
  sqlstm.sqtdso[2] = (unsigned short )0;
  sqlstm.sqhstv[3] = (unsigned char  *)&DS_IFacNum;
  sqlstm.sqhstl[3] = (unsigned long )sizeof(int);
  sqlstm.sqhsts[3] = (         int  )0;
  sqlstm.sqindv[3] = (         short *)0;
  sqlstm.sqinds[3] = (         int  )0;
  sqlstm.sqharm[3] = (unsigned long )0;
  sqlstm.sqadto[3] = (unsigned short )0;
  sqlstm.sqtdso[3] = (unsigned short )0;
  sqlstm.sqhstv[4] = (unsigned char  *)&DS_FacName;
  sqlstm.sqhstl[4] = (unsigned long )22;
  sqlstm.sqhsts[4] = (         int  )0;
  sqlstm.sqindv[4] = (         short *)0;
  sqlstm.sqinds[4] = (         int  )0;
  sqlstm.sqharm[4] = (unsigned long )0;
  sqlstm.sqadto[4] = (unsigned short )0;
  sqlstm.sqtdso[4] = (unsigned short )0;
  sqlstm.sqhstv[5] = (unsigned char  *)&DS_IOffice;
  sqlstm.sqhstl[5] = (unsigned long )32;
  sqlstm.sqhsts[5] = (         int  )0;
  sqlstm.sqindv[5] = (         short *)0;
  sqlstm.sqinds[5] = (         int  )0;
  sqlstm.sqharm[5] = (unsigned long )0;
  sqlstm.sqadto[5] = (unsigned short )0;
  sqlstm.sqtdso[5] = (unsigned short )0;
  sqlstm.sqhstv[6] = (unsigned char  *)&DS_IPhone;
  sqlstm.sqhstl[6] = (unsigned long )14;
  sqlstm.sqhsts[6] = (         int  )0;
  sqlstm.sqindv[6] = (         short *)0;
  sqlstm.sqinds[6] = (         int  )0;
  sqlstm.sqharm[6] = (unsigned long )0;
  sqlstm.sqadto[6] = (unsigned short )0;
  sqlstm.sqtdso[6] = (unsigned short )0;
  sqlstm.sqhstv[7] = (unsigned char  *)&DS_FacRank;
  sqlstm.sqhstl[7] = (unsigned long )6;
  sqlstm.sqhsts[7] = (         int  )0;
  sqlstm.sqindv[7] = (         short *)0;
  sqlstm.sqinds[7] = (         int  )0;
  sqlstm.sqharm[7] = (unsigned long )0;
  sqlstm.sqadto[7] = (unsigned short )0;
  sqlstm.sqtdso[7] = (unsigned short )0;
  sqlstm.sqhstv[8] = (unsigned char  *)&DS_FacYearStart;
  sqlstm.sqhstl[8] = (unsigned long )sizeof(int);
  sqlstm.sqhsts[8] = (         int  )0;
  sqlstm.sqindv[8] = (         short *)0;
  sqlstm.sqinds[8] = (         int  )0;
  sqlstm.sqharm[8] = (unsigned long )0;
  sqlstm.sqadto[8] = (unsigned short )0;
  sqlstm.sqtdso[8] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode == 1403) notFound();
  if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}



		if( sqlca.sqlcode == 0 )
		{
			if( queryType == "department" )
				printResultsDepartment( counter );
			else
				printResultsFaculty( counter );
		}
			
		counter++;
		
		
	}
	while( sqlca.sqlcode == 0 ); //end of retrieve do-while loop
	
	cout << endl << endl;
	counter = 0;
	
}//end of executeSQL function

void deleteEmptyDepartment( string department )
{
	int numTuples;
	//int dept = atoi(department.c_str());
	char sql[100] = "delete from department where deptname = 03";

	strcpy( (char*) sqlStatement.arr,  "SELECT FacName, DEPARTMENT.DeptName, DEPARTMENT.DeptNum, DEPARTMENT.DeptLocation "
									   "FROM   FACULTY"
									   "JOIN   INTERSECTION"
									   "ON     FACULTY.FacNum = INTERSECTION.IFacNum"
									   "JOIN   DEPARTMENT"
									   "ON     INTERSECTION.IDeptNum = DEPARTMENT.DeptNum"
									   "WHERE  DeptNum =");

	strcat( (char*) sqlStatement.arr, department.c_str() );
	 
	prepareCursor();

	numTuples = executeDeleteSQL();

	if( numTuples = 0 )
	{
		strcpy( (char*) sqlStatement.arr, "delete from department where deptname = 03");
		//prepareCursor();
		/* EXEC SQL PREPARE sqlCom FROM :sqlStatement; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 9;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.stmt = "";
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )121;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqhstv[0] = (unsigned char  *)&sqlStatement;
  sqlstm.sqhstl[0] = (unsigned long )802;
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         short *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned long )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


		
		//EXEC SQL DELETE FROM DEPARTMENT WHERE DEPTNAME = department;
		/* EXEC SQL EXECUTE sqlCom; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 9;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.stmt = "";
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )140;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode == 1403) notFound();
  if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


	
		cout << "Department:  " << DS_DeptName.arr << " was successfully deleted\n\n";

		
	}
	else if( numTuples == 2 )
	{
		cout << "department not empty\n\n\n";
	}
	else if( numTuples > 2 )
	{
		//todo: inform user that department cannot be deleted, and why
	}

}

int executeDeleteSQL()
{
	/* EXEC SQL PREPARE sqlCom FROM :sqlStatement; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 9;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.stmt = "";
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )155;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (unsigned char  *)&sqlStatement;
 sqlstm.sqhstl[0] = (unsigned long )802;
 sqlstm.sqhsts[0] = (         int  )0;
 sqlstm.sqindv[0] = (         short *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned long )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


	do
	{
		//if zero tuples were selected from DB, call the function notFound
		
		/* EXEC SQL FETCH C1 INTO  DS_FacName,
                                DS_DeptNum,
								DS_DeptName,
                                DS_DeptLocation; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 9;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )174;
  sqlstm.selerr = (unsigned short)1;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqfoff = (         int )0;
  sqlstm.sqfmod = (unsigned int )2;
  sqlstm.sqhstv[0] = (unsigned char  *)&DS_FacName;
  sqlstm.sqhstl[0] = (unsigned long )22;
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         short *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned long )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqhstv[1] = (unsigned char  *)&DS_DeptNum;
  sqlstm.sqhstl[1] = (unsigned long )sizeof(int);
  sqlstm.sqhsts[1] = (         int  )0;
  sqlstm.sqindv[1] = (         short *)0;
  sqlstm.sqinds[1] = (         int  )0;
  sqlstm.sqharm[1] = (unsigned long )0;
  sqlstm.sqadto[1] = (unsigned short )0;
  sqlstm.sqtdso[1] = (unsigned short )0;
  sqlstm.sqhstv[2] = (unsigned char  *)&DS_DeptName;
  sqlstm.sqhstl[2] = (unsigned long )32;
  sqlstm.sqhsts[2] = (         int  )0;
  sqlstm.sqindv[2] = (         short *)0;
  sqlstm.sqinds[2] = (         int  )0;
  sqlstm.sqharm[2] = (unsigned long )0;
  sqlstm.sqadto[2] = (unsigned short )0;
  sqlstm.sqtdso[2] = (unsigned short )0;
  sqlstm.sqhstv[3] = (unsigned char  *)&DS_DeptLocation;
  sqlstm.sqhstl[3] = (unsigned long )32;
  sqlstm.sqhsts[3] = (         int  )0;
  sqlstm.sqindv[3] = (         short *)0;
  sqlstm.sqinds[3] = (         int  )0;
  sqlstm.sqharm[3] = (unsigned long )0;
  sqlstm.sqadto[3] = (unsigned short )0;
  sqlstm.sqtdso[3] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode == 1403) notFound();
  if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


		
	}
	while( sqlca.sqlcode == 0 ); //end of retrieve do-while loop

	//returns # of tuples fetched from the relation
	return sqlca.sqlerrd[2];
}

//called and printed if no rows were selected from the database
//if counter is equal to 0, then it means this function was called during the 
//first iteration of the do-while loop in the previous function, prepareExecute()
void notFound( )
{

	if( counter == 0 )
		cout << "\n\nNo tuples found matching the requested statement\n\n";
}//end of notFound function

//prints the results of each department, by listing all faculty memebers of that department
 void printResultsDepartment( int counter )
 {
	 
	//prints header information for department being queried
	if( counter == 0 )
	{
		cout << "\n\n\n---------------------------------- Department -----------------------------------\n";
		
		cout << "               Depart Num :  " <<  DS_IDeptNum                 << endl;
		cout << "               Depart Name:  " << (char*) DS_DeptName.arr      << endl;
		cout << "               Location   :  " << (char*) DS_DeptLocation.arr  << endl;       
		
		cout << "----------------------------------- Faculty Member(s) ---------------------------------\n\n";
		cout << "    Name             Num         Office            Phone         Rank      Start Year\n";
	    cout << "============        =====   =================  =============    ========  ===========\n\n";
	}//end of print header if statement
	
	//prints specified variables in a uniform and formatted way
	cout << setw(20) << left << (char *) DS_FacName.arr;
	cout << setw(8)  << left << DS_IFacNum;
	cout << setw(19) << left << (char *) DS_IOffice.arr;
	cout << setw(17) << left << (char *) DS_IPhone.arr; 
	cout << setw(10) << left << (char *) DS_FacRank.arr;
	cout << setw(10) << left << DS_FacYearStart << endl;
	
	
	strcpy( (char *) DS_DeptName.arr,     "                 " );
	strcpy( (char *) DS_DeptLocation.arr, "                 " );
	strcpy( (char *) DS_IOffice.arr,      "                 " );
	strcpy( (char *) DS_FacName.arr,      "                 " );
	strcpy( (char *) DS_IPhone.arr,       "                 " );
	strcpy( (char *) DS_FacRank.arr,      "                 " );

 } //end print deparment function
 
 //prints results of each faculty memeber specified, by printing out the department information
 //for each department the faculty member is a member of.
 void printResultsFaculty( int counter )
 {
	//prints header only once. the counter variable ensures this
	if( counter == 0 )
	{
		cout << "\n\n\n-------------------------- Faculty Member ------------------------------\n";
		
		cout << "               Faculty Num:  " <<  DS_IFacNum                  << endl;
		cout << "               Name       :  " << (char*) DS_FacName.arr       << endl;
		cout << "               Rank       :  " << (char*) DS_FacRank.arr       << endl;
		cout << "               Year Start :  " <<  DS_FacYearStart             << endl;       
		
		cout << "---------------------------- Department(s)  ----------------------------\n\n";
		cout << "  Num      Department Name           Office            Location           Phone\n";
		cout << " =====   ===================    ================    ==============     =============\n";
	}//end print header if statment
	
	//prints output from variables in the declare section
	cout << "   " << setw(8)   << left << DS_IDeptNum;
	cout << setw(22)  << left << (char*) DS_DeptName.arr;
	cout << setw(19)  << left << (char *) DS_IOffice.arr;
	cout << setw(20)  << left << (char*) DS_DeptLocation.arr; 
	cout << setw(10)  << left << (char*) DS_IPhone.arr << endl;

	strcpy( (char *) DS_DeptName.arr,     "                 " );
	strcpy( (char *) DS_DeptLocation.arr, "                 " );
	strcpy( (char *) DS_IOffice.arr,      "                 " );
	strcpy( (char *) DS_FacName.arr,      "                 " );
	strcpy( (char *) DS_IPhone.arr,       "                 " );
	strcpy( (char *) DS_FacRank.arr,      "                 " );

 } //end print faculty function
 
//commits work performed on database and severs connection
void commit()
{
	/* EXEC SQL COMMIT WORK RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 9;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )205;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlErrorHandle("***Oracle Error:  ");
}


}//end commit function

//error handler used everytime an SQL error is generated in Oracle, could be an incorrect login name/password
//or an error in SQL syntax
void sqlErrorHandle( char * message )
{
	/* EXEC SQL WHENEVER SQLERROR CONTINUE; */ 

	
	cout << endl << message << endl;
	cout << sqlca.sqlerrm.sqlerrmc << endl;
	
	/* EXEC SQL ROLLBACK RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 9;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )220;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


	
	exit(1);
} //end sqlError function
